﻿USE [eBookLib]
GO
/****** Object:  ForeignKey [FK_Book_Publisher]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[Book] DROP CONSTRAINT [FK_Book_Publisher]
GO
/****** Object:  ForeignKey [FK_BookAuthor_Author]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[BookAuthor] DROP CONSTRAINT [FK_BookAuthor_Author]
GO
/****** Object:  ForeignKey [FK_BookAuthor_Book]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[BookAuthor] DROP CONSTRAINT [FK_BookAuthor_Book]
GO
/****** Object:  ForeignKey [FK_BookCategory_Book]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[BookCategory] DROP CONSTRAINT [FK_BookCategory_Book]
GO
/****** Object:  ForeignKey [FK_BookCategory_Category]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[BookCategory] DROP CONSTRAINT [FK_BookCategory_Category]
GO
/****** Object:  ForeignKey [FK_BookTopic_Book]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[BookTopic] DROP CONSTRAINT [FK_BookTopic_Book]
GO
/****** Object:  ForeignKey [FK_BookTopic_Topic]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[BookTopic] DROP CONSTRAINT [FK_BookTopic_Topic]
GO
/****** Object:  ForeignKey [FK_Link_Book]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[Link] DROP CONSTRAINT [FK_Link_Book]
GO
/****** Object:  ForeignKey [FK_SysAddInCredential_SysRole]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[SysAddInCredential] DROP CONSTRAINT [FK_SysAddInCredential_SysRole]
GO
/****** Object:  ForeignKey [FK_UserRole_Role1]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[SysUserRole] DROP CONSTRAINT [FK_UserRole_Role1]
GO
/****** Object:  ForeignKey [FK_UserRole_User]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[SysUserRole] DROP CONSTRAINT [FK_UserRole_User]
GO
/****** Object:  StoredProcedure [dbo].[spLib_Book_Delete]    Script Date: 10/05/2012 09:05:15 ******/
DROP PROCEDURE [dbo].[spLib_Book_Delete]
GO
/****** Object:  View [dbo].[v_BookInfo]    Script Date: 10/05/2012 09:05:17 ******/
DROP VIEW [dbo].[v_BookInfo]
GO
/****** Object:  Table [dbo].[BookAuthor]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[BookAuthor] DROP CONSTRAINT [FK_BookAuthor_Author]
GO
ALTER TABLE [dbo].[BookAuthor] DROP CONSTRAINT [FK_BookAuthor_Book]
GO
DROP TABLE [dbo].[BookAuthor]
GO
/****** Object:  Table [dbo].[BookCategory]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[BookCategory] DROP CONSTRAINT [FK_BookCategory_Book]
GO
ALTER TABLE [dbo].[BookCategory] DROP CONSTRAINT [FK_BookCategory_Category]
GO
DROP TABLE [dbo].[BookCategory]
GO
/****** Object:  Table [dbo].[BookTopic]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[BookTopic] DROP CONSTRAINT [FK_BookTopic_Book]
GO
ALTER TABLE [dbo].[BookTopic] DROP CONSTRAINT [FK_BookTopic_Topic]
GO
DROP TABLE [dbo].[BookTopic]
GO
/****** Object:  Table [dbo].[Link]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[Link] DROP CONSTRAINT [FK_Link_Book]
GO
ALTER TABLE [dbo].[Link] DROP CONSTRAINT [DF_Link_Version]
GO
DROP TABLE [dbo].[Link]
GO
/****** Object:  Table [dbo].[Book]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[Book] DROP CONSTRAINT [FK_Book_Publisher]
GO
ALTER TABLE [dbo].[Book] DROP CONSTRAINT [DF_Book_Version]
GO
DROP TABLE [dbo].[Book]
GO
/****** Object:  Table [dbo].[SysUserRole]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[SysUserRole] DROP CONSTRAINT [FK_UserRole_Role1]
GO
ALTER TABLE [dbo].[SysUserRole] DROP CONSTRAINT [FK_UserRole_User]
GO
DROP TABLE [dbo].[SysUserRole]
GO
/****** Object:  StoredProcedure [dbo].[spSYS_GenerateBO]    Script Date: 10/05/2012 09:05:15 ******/
DROP PROCEDURE [dbo].[spSYS_GenerateBO]
GO
/****** Object:  Table [dbo].[SysAddInCredential]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[SysAddInCredential] DROP CONSTRAINT [FK_SysAddInCredential_SysRole]
GO
DROP TABLE [dbo].[SysAddInCredential]
GO
/****** Object:  Table [dbo].[SysRole]    Script Date: 10/05/2012 09:05:17 ******/
DROP TABLE [dbo].[SysRole]
GO
/****** Object:  Table [dbo].[SysUser]    Script Date: 10/05/2012 09:05:17 ******/
DROP TABLE [dbo].[SysUser]
GO
/****** Object:  Table [dbo].[Topic]    Script Date: 10/05/2012 09:05:17 ******/
DROP TABLE [dbo].[Topic]
GO
/****** Object:  Table [dbo].[Author]    Script Date: 10/05/2012 09:05:16 ******/
ALTER TABLE [dbo].[Author] DROP CONSTRAINT [DF_Author_Version]
GO
DROP TABLE [dbo].[Author]
GO
/****** Object:  Table [dbo].[Publisher]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[Publisher] DROP CONSTRAINT [DF_Publisher_Version]
GO
DROP TABLE [dbo].[Publisher]
GO
/****** Object:  Table [dbo].[Settings]    Script Date: 10/05/2012 09:05:17 ******/
DROP TABLE [dbo].[Settings]
GO
/****** Object:  Table [dbo].[Category]    Script Date: 10/05/2012 09:05:17 ******/
DROP TABLE [dbo].[Category]
GO
/****** Object:  UserDefinedFunction [dbo].[fnSYS_TranslateSQLType]    Script Date: 10/05/2012 09:05:17 ******/
DROP FUNCTION [dbo].[fnSYS_TranslateSQLType]
GO
/****** Object:  UserDefinedFunction [dbo].[fnSYS_TranslateSQLType]    Script Date: 10/05/2012 09:05:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[fnSYS_TranslateSQLType](@typeName varchar(50),@isNullable varchar(1))
  returns varchar(50)
  as begin
    declare @result varchar(50)
        
    set @result = case when @typeName in ('varchar', 'nvarchar', 'sysname', 'text') then 'string'
                       when @typeName in ('datetime', 'smalldatetime')              then 'DateTime' 
                       when @typeName in ('int')                                    then 'int'                        
                       when @typeName in ('smallint')                               then 'short'
                       when @typeName = 'bigint'                                    then 'long'    
                       when @typeName = 'bit'                                       then 'bool'
                       when @typeName = 'uniqueidentifier'                          then 'Guid'
                       else @typeName 
                  end
    
    if @isNullable = '1' and @result <> 'string'
      set @result += '?' 
    
    return @result
  end
GO
/****** Object:  Table [dbo].[Category]    Script Date: 10/05/2012 09:05:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Category](
	[CategoryID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
	[CategoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Settings]    Script Date: 10/05/2012 09:05:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Settings](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](250) NOT NULL,
	[Value] [varchar](250) NULL,
 CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Publisher]    Script Date: 10/05/2012 09:05:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Publisher](
	[PublisherID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](250) NULL,
	[ISBNChunk] [varchar](50) NULL,
	[DateCreated] [smalldatetime] NULL,
	[DateModified] [smalldatetime] NULL,
	[Version] [int] NOT NULL CONSTRAINT [DF_Publisher_Version]  DEFAULT ((1)),
 CONSTRAINT [PK_Publisher] PRIMARY KEY CLUSTERED 
(
	[PublisherID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Author]    Script Date: 10/05/2012 09:05:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Author](
	[AuthorID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](250) NULL,
	[DateOfBirth] [smalldatetime] NULL,
	[DateOfDeath] [smalldatetime] NULL,
	[Version] [int] NOT NULL CONSTRAINT [DF_Author_Version]  DEFAULT ((1)),
 CONSTRAINT [PK_Author] PRIMARY KEY CLUSTERED 
(
	[AuthorID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Topic]    Script Date: 10/05/2012 09:05:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Topic](
	[TopicID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](250) NOT NULL,
 CONSTRAINT [PK_Topic] PRIMARY KEY CLUSTERED 
(
	[TopicID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[SysUser]    Script Date: 10/05/2012 09:05:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SysUser](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[Username] [varchar](50) NOT NULL,
	[EMail] [varchar](1024) NOT NULL,
	[Pwd] [text] NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	[IsActive] [bit] NULL,
	[CanChangePwd] [bit] NULL,
	[ForceChangePwd] [bit] NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[SysRole]    Script Date: 10/05/2012 09:05:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SysRole](
	[RoleID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[NormalizedName]  AS (rtrim(ltrim(lower([Name])))),
	[Description] [varchar](200) NULL,
 CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED 
(
	[RoleID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_SysRole] ON [dbo].[SysRole] 
(
	[Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[SysAddInCredential]    Script Date: 10/05/2012 09:05:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SysAddInCredential](
	[AddInCredentialID] [int] IDENTITY(1,1) NOT NULL,
	[AddInID] [int] NULL,
	[RoleID] [int] NULL,
 CONSTRAINT [PK_SysAddInCredential] PRIMARY KEY CLUSTERED 
(
	[AddInCredentialID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_SysAddInCredential] ON [dbo].[SysAddInCredential] 
(
	[AddInID] ASC,
	[RoleID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[spSYS_GenerateBO]    Script Date: 10/05/2012 09:05:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[spSYS_GenerateBO] @tableName varchar(50)
as begin
  
  declare @script varchar(5000) = ''
  declare @CRLF varchar(50) = char(13) + char(10)
  declare @TAB varchar(50) = char(9)
  
  set @script = 'using System;'
              + @CRLF              
              + 'using System.Collections.Generic;'
              + @CRLF
              + 'using System.Linq;'
              + @CRLF
              + 'using System.Text;'
              + @CRLF
              + 'using System.ComponentModel;' 
              + @CRLF
              + 'using System.ComponentModel.DataAnnotations;'
              + @CRLF + @CRLF             
              + 'namespace '
              + @CRLF              
              + '{'
              + @CRLF + @TAB              
              + 'public class bo' + @tableName
              + @CRLF + @TAB              
              + '{'
              + @CRLF              
/*    
  SELECT table_name  = sysobjects.name,
         column_name = syscolumns.name,
         datatype = systypes.name,
         length   = syscolumns.length,                           
         property_declaration = case when syscolumns.isnullable = '0' then '[Required] '
                                     else ' '
                                end
                              + '[DisplayName("' + syscolumns.name +'")] '
                              + 'public ' 
                              + dbo.fnSYS_TranslateSQLType(systypes.name, syscolumns.isnullable) 
                              + ' ' 
                              + syscolumns.name 
                              + ' {get; set;}'        
    FROM sysobjects 
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
   WHERE sysobjects.xtype='U' 
     and systypes.name <> 'sysname'
     and sysobjects.name = 'User'
ORDER BY sysobjects.name,syscolumns.colid
*/

  SELECT @script = @script 
                 
                 + case when syscolumns.isnullable = '0' then  @CRLF + @TAB + @TAB+ '[Required] '
                        else ' '
                   end
                 + @CRLF + @TAB + @TAB 
                 + '[DisplayName("' + syscolumns.name +'")] '
                 + @CRLF + @TAB + @TAB               
                 + 'public ' 
                 + dbo.fnSYS_TranslateSQLType(systypes.name, syscolumns.isnullable)   
                 + ' '           
                 + syscolumns.name 
                 + ' {get; set;}'        
                 + @CRLF                 
    FROM sysobjects 
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
    WHERE sysobjects.xtype in ('U', 'V')
      and systypes.name <> 'sysname'
      and sysobjects.name = @tableName
    ORDER BY sysobjects.name,syscolumns.colid


  SELECT @script = @script 
                 + '}'
                 + @CRLF+ @CRLF+ @CRLF+ @CRLF
                 + '}'
  
  print @script

end
GO
/****** Object:  Table [dbo].[SysUserRole]    Script Date: 10/05/2012 09:05:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SysUserRole](
	[UserRoleID] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [int] NOT NULL,
	[RoleID] [int] NOT NULL,
 CONSTRAINT [PK_UserRole] PRIMARY KEY CLUSTERED 
(
	[UserRoleID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_SysUserRole] ON [dbo].[SysUserRole] 
(
	[RoleID] ASC,
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Book]    Script Date: 10/05/2012 09:05:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Book](
	[BookID] [int] IDENTITY(1,1) NOT NULL,
	[ISBN10] [varchar](50) NULL,
	[ISBN13] [varchar](50) NULL,
	[ASIN] [varchar](50) NULL,
	[LCCN] [varchar](50) NULL,
	[DeweyDecimal] [varchar](50) NULL,
	[Title] [varchar](250) NULL,
	[Subtitle] [varchar](250) NULL,
	[PublisherID] [int] NULL,
	[NumberOfPages] [int] NULL,
	[Edition] [varchar](50) NULL,
	[AmazonRating] [varchar](50) NULL,
	[EditorialReview] [varchar](7000) NULL,
	[PublicationDate] [varchar](50) NULL,
	[SmallImageURL] [varchar](250) NULL,
	[MediumImageURL] [varchar](250) NULL,
	[LargeImageURL] [varchar](250) NULL,
	[FilePath] [varchar](250) NULL,
	[FileName] [varchar](250) NULL,
	[DateCreated] [smalldatetime] NULL,
	[DateModified] [smalldatetime] NULL,
	[Version] [int] NOT NULL CONSTRAINT [DF_Book_Version]  DEFAULT ((1)),
	[Path] [varchar](250) NULL,
 CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED 
(
	[BookID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [IX_ASIN] ON [dbo].[Book] 
(
	[ASIN] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_FilePath] ON [dbo].[Book] 
(
	[FilePath] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ISBN] ON [dbo].[Book] 
(
	[ISBN13] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Publisher] ON [dbo].[Book] 
(
	[PublisherID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Link]    Script Date: 10/05/2012 09:05:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Link](
	[LinkID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](100) NULL,
	[Path] [varchar](250) NULL,
	[BookID] [int] NULL,
	[DateCreated] [smalldatetime] NULL,
	[DateModified] [smalldatetime] NULL,
	[Version] [int] NOT NULL CONSTRAINT [DF_Link_Version]  DEFAULT ((1)),
 CONSTRAINT [PK_Link] PRIMARY KEY CLUSTERED 
(
	[LinkID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[BookTopic]    Script Date: 10/05/2012 09:05:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BookTopic](
	[BookTopicID] [int] IDENTITY(1,1) NOT NULL,
	[BookID] [int] NULL,
	[TopicID] [int] NULL,
 CONSTRAINT [PK_BookTopic] PRIMARY KEY CLUSTERED 
(
	[BookTopicID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[BookCategory]    Script Date: 10/05/2012 09:05:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BookCategory](
	[BookCategoryID] [int] IDENTITY(1,1) NOT NULL,
	[BookID] [int] NULL,
	[CategoryID] [int] NULL,
 CONSTRAINT [PK_BookCategory] PRIMARY KEY CLUSTERED 
(
	[BookCategoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[BookAuthor]    Script Date: 10/05/2012 09:05:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BookAuthor](
	[BookAuthorID] [int] IDENTITY(1,1) NOT NULL,
	[BookID] [int] NULL,
	[AuthorID] [int] NULL,
 CONSTRAINT [PK_BookAuthor] PRIMARY KEY CLUSTERED 
(
	[BookAuthorID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Author] ON [dbo].[BookAuthor] 
(
	[AuthorID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Book] ON [dbo].[BookAuthor] 
(
	[BookID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** Object:  View [dbo].[v_BookInfo]    Script Date: 10/05/2012 09:05:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[v_BookInfo]
as 
  select B.BookId, B.Title 
       , B.ISBN10, B.ISBN13, B.LCCN, B.DeweyDecimal
       , B.PublisherID, P.Name
  from Book B
  left join Publisher P on B.PublisherId = P.PubLisherId
GO
/****** Object:  StoredProcedure [dbo].[spLib_Book_Delete]    Script Date: 10/05/2012 09:05:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[spLib_Book_Delete] @BookID int
as begin
  delete from BookAuthor where BookID = @BookID
  delete from Link where BookID = @BookID
  delete from Book where BookID = @BookID
end
GO
/****** Object:  ForeignKey [FK_Book_Publisher]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[Book]  WITH CHECK ADD  CONSTRAINT [FK_Book_Publisher] FOREIGN KEY([PublisherID])
REFERENCES [dbo].[Publisher] ([PublisherID])
GO
ALTER TABLE [dbo].[Book] CHECK CONSTRAINT [FK_Book_Publisher]
GO
/****** Object:  ForeignKey [FK_BookAuthor_Author]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[BookAuthor]  WITH CHECK ADD  CONSTRAINT [FK_BookAuthor_Author] FOREIGN KEY([AuthorID])
REFERENCES [dbo].[Author] ([AuthorID])
GO
ALTER TABLE [dbo].[BookAuthor] CHECK CONSTRAINT [FK_BookAuthor_Author]
GO
/****** Object:  ForeignKey [FK_BookAuthor_Book]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[BookAuthor]  WITH CHECK ADD  CONSTRAINT [FK_BookAuthor_Book] FOREIGN KEY([BookID])
REFERENCES [dbo].[Book] ([BookID])
GO
ALTER TABLE [dbo].[BookAuthor] CHECK CONSTRAINT [FK_BookAuthor_Book]
GO
/****** Object:  ForeignKey [FK_BookCategory_Book]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[BookCategory]  WITH CHECK ADD  CONSTRAINT [FK_BookCategory_Book] FOREIGN KEY([BookID])
REFERENCES [dbo].[Book] ([BookID])
GO
ALTER TABLE [dbo].[BookCategory] CHECK CONSTRAINT [FK_BookCategory_Book]
GO
/****** Object:  ForeignKey [FK_BookCategory_Category]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[BookCategory]  WITH CHECK ADD  CONSTRAINT [FK_BookCategory_Category] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
GO
ALTER TABLE [dbo].[BookCategory] CHECK CONSTRAINT [FK_BookCategory_Category]
GO
/****** Object:  ForeignKey [FK_BookTopic_Book]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[BookTopic]  WITH CHECK ADD  CONSTRAINT [FK_BookTopic_Book] FOREIGN KEY([BookID])
REFERENCES [dbo].[Book] ([BookID])
GO
ALTER TABLE [dbo].[BookTopic] CHECK CONSTRAINT [FK_BookTopic_Book]
GO
/****** Object:  ForeignKey [FK_BookTopic_Topic]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[BookTopic]  WITH CHECK ADD  CONSTRAINT [FK_BookTopic_Topic] FOREIGN KEY([TopicID])
REFERENCES [dbo].[Topic] ([TopicID])
GO
ALTER TABLE [dbo].[BookTopic] CHECK CONSTRAINT [FK_BookTopic_Topic]
GO
/****** Object:  ForeignKey [FK_Link_Book]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[Link]  WITH CHECK ADD  CONSTRAINT [FK_Link_Book] FOREIGN KEY([BookID])
REFERENCES [dbo].[Book] ([BookID])
GO
ALTER TABLE [dbo].[Link] CHECK CONSTRAINT [FK_Link_Book]
GO
/****** Object:  ForeignKey [FK_SysAddInCredential_SysRole]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[SysAddInCredential]  WITH CHECK ADD  CONSTRAINT [FK_SysAddInCredential_SysRole] FOREIGN KEY([RoleID])
REFERENCES [dbo].[SysRole] ([RoleID])
GO
ALTER TABLE [dbo].[SysAddInCredential] CHECK CONSTRAINT [FK_SysAddInCredential_SysRole]
GO
/****** Object:  ForeignKey [FK_UserRole_Role1]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[SysUserRole]  WITH CHECK ADD  CONSTRAINT [FK_UserRole_Role1] FOREIGN KEY([RoleID])
REFERENCES [dbo].[SysRole] ([RoleID])
GO
ALTER TABLE [dbo].[SysUserRole] CHECK CONSTRAINT [FK_UserRole_Role1]
GO
/****** Object:  ForeignKey [FK_UserRole_User]    Script Date: 10/05/2012 09:05:17 ******/
ALTER TABLE [dbo].[SysUserRole]  WITH CHECK ADD  CONSTRAINT [FK_UserRole_User] FOREIGN KEY([UserID])
REFERENCES [dbo].[SysUser] ([UserID])
GO
ALTER TABLE [dbo].[SysUserRole] CHECK CONSTRAINT [FK_UserRole_User]
GO
